package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.edu.bean.News;
import com.edu.bean.Orders;
import com.edu.dao.BaseDao;
import com.edu.dao.NewsDao;

public class NewsDaoImpl implements NewsDao {
	@Override
	public List<News> getAllNews(int pageNum,int pageSize) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<News> newsList = new ArrayList();
		String sql = "SELECT * FROM news ORDER BY publish_date DESC LIMIT ?,?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, (pageNum-1)*pageSize);
			ps.setInt(2, pageSize);
			res = ps.executeQuery();
			while(res.next()) {
				News news = new News();
                news.setId(res.getInt("id"));
                news.setTitle(res.getString("title"));
                news.setContent(res.getString("content"));
                news.setPublishDate(res.getDate("publish_date"));
                news.setAuthor(res.getString("author"));
                news.setImageUrl(res.getString("image_url"));
                newsList.add(news);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		return newsList;
	}

	@Override
	public int addNews(News news) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql="";
			if(news.getImageUrl()==null) {
				sql = "INSERT INTO news(title,content,publish_date,author) VALUES (?,?,?,?)";
			}else {
				sql = "INSERT INTO news(title,content,publish_date,author,image_url) VALUES (?,?,?,?,?)";
			}
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, news.getTitle());
			ps.setString(2, news.getContent());
			ps.setDate(3, new Date(news.getPublishDate().getTime()));
			ps.setString(4, news.getAuthor());
			if(news.getImageUrl()!=null) {
				ps.setString(5, news.getImageUrl());
			}
			result = ps.executeUpdate();
			System.out.println(ps);
		} catch (SQLException e) {
			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public int deleteNews(int id) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "DELETE FROM news WHERE id = ?";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			result = ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		
		return result;
	}

	@Override
	public List<News> getLatestNews(int count) {
		 String sql = "SELECT * FROM news ORDER BY publish_date DESC LIMIT ?";
		    List<News> newsList = new ArrayList<>();
		    try (Connection conn = BaseDao.getCon();
		         PreparedStatement stmt = conn.prepareStatement(sql)) {
		        stmt.setInt(1, count);
		        try (ResultSet rs = stmt.executeQuery()) {
		            while (rs.next()) {
		                News news = new News();
		                news.setId(rs.getInt("id"));
		                news.setTitle(rs.getString("title"));
		                news.setContent(rs.getString("content"));
		                news.setAuthor(rs.getString("author"));
		                Timestamp timestamp = rs.getTimestamp("publish_date");
		                if (timestamp != null) {
		                    news.setPublishDate(timestamp);
		                }
		                
		                news.setImageUrl(rs.getString("image_url"));
		                
		                newsList.add(news);
		            }
		        }
		    } catch (SQLException e) {
		        throw new RuntimeException("获取最新新闻失败", e);
		    }
		    
		    return newsList;
	}

	@Override
	public List<News> searchNewsByTitle(String keyword) {
		List<News> newsList = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = BaseDao.getCon();
            String sql = "SELECT * FROM news WHERE title LIKE ? ORDER BY publish_date DESC";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + keyword + "%");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                News news = new News();
                news.setId(rs.getInt("id"));
                news.setTitle(rs.getString("title"));
                news.setContent(rs.getString("content"));
                news.setPublishDate(rs.getDate("publish_date"));
                news.setAuthor(rs.getString("author"));
                news.setImageUrl(rs.getString("image_url"));
                newsList.add(news);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
        	BaseDao.closeAll(conn, pstmt, rs);
        }
        return newsList;
	}

	@Override
	public int selectNewsall(News news) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		int result = 0;
		String sql = "SELECT count(1) as total FROM news";
		List<Object> obj = new ArrayList<Object>();
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			if(res.next()) {
				result = res.getInt("total");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return result;
	}

	@Override
	public int updatenews(News news) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE news set "
				+ "title = ?,content = ?,publish_date = ?,author = ?";
				if(news.getImageUrl()!=null) {
					sql +=",image_url = ?";
				}
				sql+= " WHERE id = ? ";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, news.getTitle());
			ps.setString(2, news.getContent());
			ps.setDate(3, new Date(news.getPublishDate().getTime()));
			ps.setString(4, news.getAuthor());
			if(news.getImageUrl()!=null) {
				ps.setString(5, news.getImageUrl());
				ps.setInt(6, news.getId());
			}else {
				ps.setInt(5, news.getId());
			}
			
			result = ps.executeUpdate();
			System.out.println(ps);
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public News selectnewsid(int id) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		News news=null;
		String sql = "SELECT * FROM news WHERE id = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,id);
			res = ps.executeQuery();
			if(res.next()) {
				  news = new News();
	              news.setId(res.getInt("id"));
	              news.setTitle(res.getString("title"));
	              news.setContent(res.getString("content"));
	              news.setPublishDate(res.getDate("publish_date"));
	              news.setAuthor(res.getString("author"));
	              news.setImageUrl(res.getString("image_url"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return news;
	}

}
